package com.example.uwe.service;

import com.example.uwe.model.*;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class AccommodationService {
    private static final Logger logger = LoggerFactory.getLogger(AccommodationService.class);
    private final ObservableList<Hall> halls = FXCollections.observableArrayList();
    private final Map<Long, RentalAgreement> rentalAgreements = new HashMap<>();
    private Connection connection = null;
    
    private static final String DB_URL = "jdbc:mysql://localhost:3306/uwedatabase";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "Liangyh040522";

    public AccommodationService() {
        try {
            this.connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            logger.info("成功连接到数据库");
            initializeData();
        } catch (SQLException e) {
            logger.error("数据库连接失败", e);
        }
    }
    
    private void initializeData() {
        try {
            // 从数据库加载宿舍楼数据
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM halls");
            
            while (rs.next()) {
                Hall hall = new Hall(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("phone_number"),
                    rs.getString("manager_name")
                );
                
                // 加载宿舍楼的住宿信息
                loadHallAccommodationsFromDB(hall);
                halls.add(hall);
                logger.info("加载宿舍楼: {}", hall.getName());
            }
            
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            logger.error("加载宿舍数据失败", e);
        }
    }
    
    private void loadHallAccommodationsFromDB(Hall hall) {
        try {
            PreparedStatement pstmt = connection.prepareStatement(
                "SELECT * FROM accommodations WHERE hall_id = (SELECT id FROM halls WHERE name = ?)");
            pstmt.setString(1, hall.getName());
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Accommodation accommodation = new Accommodation(
                    rs.getInt("accommodation_number"),
                    AccommodationType.valueOf(rs.getString("type")),
                    rs.getDouble("rental_price"),
                    rs.getBoolean("available"),
                    CleaningStatus.valueOf(rs.getString("cleaning_status")),
                    rs.getString("description"),
                    hall
                );
                hall.addAccommodation(accommodation);
            }
            
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            logger.error("加载宿舍 {} 的住宿信息失败", hall.getName(), e);
        }
    }
    

    
    public ObservableList<Hall> getHalls() {
        return halls;
    }
    
    public Hall getHallByName(String name) {
        return halls.stream()
                .filter(hall -> hall.getName().equals(name))
                .findFirst()
                .orElse(null);
    }
    
    public List<Accommodation> getAccommodationsByHall(Hall hall) {
        return new ArrayList<>(hall.getAccommodations());
    }
    
    public RentalAgreement createRentalAgreement(Accommodation accommodation, String firstName, 
                                                String lastName, String studentNumber, 
                                                String mobileNumber) {
        if (!accommodation.isAvailable() || accommodation.getCleaningStatus() != CleaningStatus.CLEAN) {
            logger.warn("无法创建租赁协议，住宿不可用或未清洁: {}", accommodation.getAccommodationNumber());
            return null;
        }
        
        try {
            PreparedStatement pstmt = connection.prepareStatement(
                "INSERT INTO rental_agreements (lease_number, accommodation_id, " +
                "first_name, last_name, student_number, mobile_number) VALUES (?, (SELECT id FROM accommodations WHERE accommodation_number = ? AND hall_id = (SELECT id FROM halls WHERE name = ?)), ?, ?, ?, ?)");
            
            RentalAgreement agreement = new RentalAgreement(
                0, // id
                (int) System.currentTimeMillis(), // leaseNumber
                0, // accommodationId
                accommodation, 
                firstName, 
                lastName, 
                studentNumber, 
                mobileNumber,
                new java.sql.Date(System.currentTimeMillis())
            );
                
            pstmt.setLong(1, agreement.getLeaseNumber());
            pstmt.setInt(2, accommodation.getAccommodationNumber());
            pstmt.setString(3, accommodation.getHall().getName());
            pstmt.setString(4, firstName);
            pstmt.setString(5, lastName);
            pstmt.setString(6, studentNumber);
            pstmt.setString(7, mobileNumber);
            
            pstmt.executeUpdate();
            pstmt.close();
            
            // 更新住宿状态
            updateAccommodationStatus(accommodation, false, CleaningStatus.CLEAN);
            
            rentalAgreements.put(agreement.getLeaseNumber(), agreement);
            logger.info("成功创建租赁协议: {}", agreement.getLeaseNumber());
            return agreement;
        } catch (SQLException e) {
            logger.error("创建租赁协议失败", e);
            return null;
        }
    }
    
    public boolean deleteRentalAgreement(long leaseNumber) {
        try {
            PreparedStatement pstmt = connection.prepareStatement(
                "DELETE FROM rental_agreements WHERE lease_number = ?");
            pstmt.setLong(1, leaseNumber);
            
            int affectedRows = pstmt.executeUpdate();
            pstmt.close();
            
            if (affectedRows > 0) {
                RentalAgreement agreement = rentalAgreements.remove(leaseNumber);
                if (agreement != null) {
                    // 更新住宿状态
                    updateAccommodationStatus(agreement.getAccommodation(), true, CleaningStatus.DIRTY);
                    logger.info("成功删除租赁协议: {}", leaseNumber);
                    return true;
                }
            }
            return false;
        } catch (SQLException e) {
            logger.error("删除租赁协议失败: {}", leaseNumber, e);
            return false;
        }
    }
    
    public void updateCleaningStatus(Accommodation accommodation, CleaningStatus status) {
        try {
            PreparedStatement pstmt = connection.prepareStatement(
                "UPDATE accommodations SET cleaning_status = ? " +
                "WHERE accommodation_number = ? AND hall_id = (SELECT id FROM halls WHERE name = ?)");
                
            pstmt.setString(1, status.name());
            pstmt.setInt(2, accommodation.getAccommodationNumber());
            pstmt.setString(3, accommodation.getHall().getName());
            
            pstmt.executeUpdate();
            pstmt.close();
            
            accommodation.setCleaningStatus(status);
            logger.info("更新清洁状态: 宿舍楼 {} 房间 {} 状态更新为 {}", 
                accommodation.getHall().getName(), 
                accommodation.getAccommodationNumber(), 
                status);
        } catch (SQLException e) {
            logger.error("更新清洁状态失败", e);
        }
    }
    
    public void updateDescription(Accommodation accommodation, String description) {
        try {
            PreparedStatement pstmt = connection.prepareStatement(
                "UPDATE accommodations SET description = ? " +
                "WHERE accommodation_number = ? AND hall_id = (SELECT id FROM halls WHERE name = ?)");
                
            pstmt.setString(1, description);
            pstmt.setInt(2, accommodation.getAccommodationNumber());
            pstmt.setString(3, accommodation.getHall().getName());
            
            pstmt.executeUpdate();
            pstmt.close();
            
            accommodation.setDescription(description);
            logger.info("更新描述: 宿舍楼 {} 房间 {} 描述更新为 {}", 
                accommodation.getHall().getName(), 
                accommodation.getAccommodationNumber(), 
                description);
        } catch (SQLException e) {
            logger.error("更新描述失败", e);
        }
    }
    
    private void updateAccommodationStatus(Accommodation accommodation, boolean isAvailable, CleaningStatus status) {
        try {
            // First get the hall_id to avoid referencing accommodations table in subquery
            PreparedStatement hallStmt = connection.prepareStatement(
                "SELECT id FROM halls WHERE name = ?");
            hallStmt.setString(1, accommodation.getHall().getName());
            ResultSet hallRs = hallStmt.executeQuery();
            
            if (hallRs.next()) {
                int hallId = hallRs.getInt("id");
                hallRs.close();
                hallStmt.close();
                
                // Now update the accommodation with the retrieved hall_id
                PreparedStatement pstmt = connection.prepareStatement(
                    "UPDATE accommodations SET available = ?, cleaning_status = ? " +
                    "WHERE accommodation_number = ? AND hall_id = ?");
                    
                pstmt.setBoolean(1, isAvailable);
                pstmt.setString(2, status.name());
                pstmt.setInt(3, accommodation.getAccommodationNumber());
                pstmt.setInt(4, hallId);
                
                pstmt.executeUpdate();
                pstmt.close();
            }
            
            accommodation.setAvailable(isAvailable);
            accommodation.setCleaningStatus(status);
        } catch (SQLException e) {
            logger.error("更新住宿状态失败", e);
        }
    }
    
    public Map<Long, RentalAgreement> getRentalAgreements() {
        return rentalAgreements;
    }
    
    public RentalAgreement getRentalAgreementByAccommodation(Accommodation accommodation) {
        return rentalAgreements.values().stream()
                .filter(agreement -> agreement.getAccommodation().equals(accommodation))
                .findFirst()
                .orElse(null);
    }
    
    public Accommodation getAccommodationByNumber(int accommodationNumber) {
        for (Hall hall : halls) {
            for (Accommodation accommodation : hall.getAccommodations()) {
                if (accommodation.getAccommodationNumber() == accommodationNumber) {
                    return accommodation;
                }
            }
        }
        return null;
    }
}